package com.cloudfast.manager.impl;

import java.io.*;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.cloudfast.utils.excel.annotation.ImportExcelCell;
import com.cloudfast.utils.excel.annotation.ImportExcelEntity;
import com.cloudfast.utils.excel.event.ExcelListenEvent;
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.cloudfast.manager.ExcelManager;
import com.cloudfast.utils.excel.AnnoTabtionData;
import com.cloudfast.utils.excel.ExcelData;
import com.cloudfast.utils.excel.ExcelParams;
import com.cloudfast.utils.excel.ExcelReplaceParams;
import com.cloudfast.utils.excel.annotation.ExcelCell;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * excel 操作实现类
 *
 * @author liuyw
 */
public class ExcelManagerImpl extends BaseManagerImpl implements ExcelManager {

    private Gson gson = null;

    private static final Logger logger = LoggerFactory.getLogger(Logger.class);

    public ExcelManagerImpl() {
        gson = new GsonBuilder().setDateFormat("yyyy-MM-dd HH:mm:ss").create();

    }

    @Override
    public <T> byte[] appendToExcel(String filePath, ExcelParams<T> excelData) {
        if (filePath.endsWith(".xls") || filePath.endsWith(".xlsx")) {
            FileInputStream fs = null;
            try {
                filePath = removeLocalHome(filePath);
                fs = new FileInputStream(this.localStoragePath(filePath));
                if (filePath.endsWith(".xls")) {
                    filePath = optXlsFile(filePath, fs, excelData);
                } else if (filePath.endsWith(".xlsx")) {
                    filePath = optXlsxFile(filePath, fs, excelData);
                }
                return fileToByte(removeLocalHome(filePath));

            } catch (Exception e) {
                logger.error(e.getMessage());
            } finally {
                try {
                    if (fs != null) {
                        fs.close();
                    }
                } catch (Exception e2) {
                    logger.error(e2.getMessage());

                }
            }
        }
        return null;
    }

    @Override
    public byte[] replaceExcel(String filePath, ExcelReplaceParams params) {
        if (filePath.endsWith(".xls") || filePath.endsWith(".xlsx")) {
            try {
                filePath = removeLocalHome(filePath);
                if (filePath.endsWith(".xls")) {
                    filePath = replaceXls(filePath, params);
                } else if (filePath.endsWith(".xlsx")) {
                    filePath = replaceXlsx(filePath, params);
                }

            } catch (Exception e) {
                logger.error(e.getMessage());
            }
        }
        return fileToByte(removeLocalHome(filePath));
    }

    @Override
    public <T> byte[] createExcel(String fileName, ExcelParams<T> excelData) {
        OutputStream fileOut = null;
        try {
            fileName = removeLocalHome(fileName);
            File file = new File(this.localStoragePath(fileName));
            if (!file.getParentFile().exists()) {
                file.mkdirs();
            }
            if (file.exists()) {
                file.delete();
            }

            fileOut = new FileOutputStream(file);
            Workbook wb = null;
            if (fileName.endsWith(".xls")) {
                wb = new HSSFWorkbook();
            } else {
                wb = new XSSFWorkbook();
            }

            // 遍历创建sheet
            for (String key : excelData.getSheetData().keySet()) {
                wb.createSheet(key);
            }
            wb.write(fileOut);
            fileOut.flush();
            wb.close();
            return this.appendToExcel(fileName, excelData);

        } catch (Exception e) {
            logger.error(e.getMessage());
        } finally {
            try {
                if (fileOut != null) {
                    fileOut.close();
                }
            } catch (Exception e2) {
                logger.error(e2.getMessage());
            }
        }
        return null;
    }

    @SuppressWarnings("unchecked")
    @Override
    public <T> List<T> importExcelData(String fileName, T entity) {
        List<T> resultList = null;
        POIFSFileSystem fs = null;
        HSSFWorkbook wb = null;
        try {
            Class<T> mEntityClass = (Class<T>) entity.getClass();
            // 获取excel数据开始的行数
            int startDataRow = findEntityExcelImportStartRowIndex(entity.getClass());
            // 获取实体类对应的列
            Map<String, Integer> fieldMap = findImportExcelEntityCellIndex(mEntityClass);

            InputStream fileInputStream = new FileInputStream(fileName);
            fs = new POIFSFileSystem(fileInputStream);
            wb = new HSSFWorkbook(fs);
            // 遍历输入
            if (startDataRow > 0) {
                resultList = new ArrayList<>();
                HSSFSheet sheet = wb.getSheetAt(0);
                int rowNum = sheet.getLastRowNum();
                for (int i = startDataRow; i < rowNum; i++) {
                    Map<String, Object> target = new HashMap<>();
                    HSSFRow rowData = sheet.getRow(i);
                    for (Map.Entry<String, Integer> entry : fieldMap.entrySet()) {
                        target.put(entry.getKey(), getCellValue(rowData.getCell(entry.getValue())));
                    }
                    T fromJson = (T) gson.fromJson(new JSONObject(target).toString(), mEntityClass);
                    resultList.add(fromJson);
                }
            }

        } catch (Exception e) {
            logger.error(e.getMessage());
        }
        return resultList;
    }

    @SuppressWarnings("unchecked")
    @Override
    public <T> void importExcelData(String fileName, T entity, ExcelListenEvent<T> event) {
        POIFSFileSystem fs = null;
        Workbook wb = null;
        try {
            Class<T> mEntityClass = (Class<T>) entity.getClass();
            // 获取excel数据开始的行数
            int startDataRow = findEntityExcelImportStartRowIndex(entity.getClass());
            // 获取实体类对应的列
            Map<String, Integer> fieldMap = findImportExcelEntityCellIndex(mEntityClass);

            InputStream fileInputStream = new FileInputStream(fileName);
            wb = WorkbookFactory.create(fileInputStream);
            // 遍历输入
            if (startDataRow > 0) {
                Sheet sheet = wb.getSheetAt(0);
                int rowNum = sheet.getLastRowNum();
                for (int i = startDataRow; i < rowNum; i++) {
                    Map<String, Object> target = new HashMap<>();
                    Row rowData = sheet.getRow(i);
                    for (Map.Entry<String, Integer> entry : fieldMap.entrySet()) {
                        target.put(entry.getKey(), getCellValue(rowData.getCell(entry.getValue())));
                    }
                    T fromJson = (T) gson.fromJson(new JSONObject(target).toString(), mEntityClass);
                    event.fillData(fromJson);
                }
            }

        } catch (Exception e) {
            logger.error(e.getMessage());
        }
    }

    /**
     * 获取导入实体字段
     *
     * @param clazz
     * @param <T>
     * @return
     */
    private <T> Map<String, Integer> findImportExcelEntityCellIndex(Class<T> clazz) {
        Map<String, Integer> map = new HashMap<>();
        try {
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                ImportExcelCell cell = field.getAnnotation(ImportExcelCell.class);
                if (cell != null) {
                    map.put(field.getName(), cell.cellIndex());
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
        return map;
    }

    /**
     * 获取导入数据开始的行
     *
     * @param clazz
     * @param <T>
     * @return
     */
    private <T> int findEntityExcelImportStartRowIndex(Class<T> clazz) {
        ImportExcelEntity importExcelEntity = clazz.getAnnotation(ImportExcelEntity.class);
        if (importExcelEntity != null && importExcelEntity.rowIndex() >= 0) {
            return importExcelEntity.rowIndex();
        }
        return -1;
    }

    /**
     * 获取excel 头
     *
     * @param mClassEntity
     * @return
     */
    private List<AnnoTabtionData> findExcelTitle(Class<?> mClassEntity) {
        Field[] fields = mClassEntity.getDeclaredFields();
        List<AnnoTabtionData> list = new ArrayList<>();
        for (Field field : fields) {
            ExcelCell cell = field.getAnnotation(ExcelCell.class);
            if (cell != null) {
                AnnoTabtionData annotationData = new AnnoTabtionData(cell.cellName(), field.getName(),
                        cell.cellIndex(), cell.cellWidth());
                list.add(annotationData);

            }
        }
        return list;
    }

    /**
     * 追加文件
     *
     * @param <T>
     * @param filePath
     * @param inputStream
     * @param excelParams
     * @throws Exception
     */
    private <T> String optXlsFile(String filePath, FileInputStream inputStream, ExcelParams<T> excelParams)
            throws Exception {
        POIFSFileSystem ps = new POIFSFileSystem(inputStream); // 使用POI提供的方法得到excel的信息
        FileOutputStream out = null;
        try (HSSFWorkbook wb = new HSSFWorkbook(ps)) {
            // 遍历sheet页
            if (excelParams.getSheetData() != null) {
                for (String sheetName : excelParams.getSheetData().keySet()) {
                    ExcelData<T> excelData = excelParams.getSheetData().get(sheetName);
                    HSSFSheet sheet = wb.getSheet(sheetName); // 获取到工作表，因为一个excel可能有多个工作表
                    HSSFRow row = sheet.getRow(0);
                    int hang = 0;
                    if ("".equals(row) || row == null) {
                        hang = 0;
                    } else {
                        hang = sheet.getLastRowNum();
                        hang = hang + 1;
                    }
                    // 反射数据生成 title 头部标签
                    List<AnnoTabtionData> list = findExcelTitle(excelData.getmClassEntity());
                    // 分别得到最后一行的行号，和一条记录的最后一个单元格

                    row = sheet.createRow((short) (hang)); // 在现有行号后追加数据
                    HSSFCell cell = null;
                    // 创建样式
                    HSSFCellStyle alignStyle = (HSSFCellStyle) wb.createCellStyle();
                    alignStyle.setAlignment(HorizontalAlignment.CENTER);
                    alignStyle.setBorderBottom(BorderStyle.THIN);
                    alignStyle.setBorderTop(BorderStyle.THIN);
                    alignStyle.setBorderLeft(BorderStyle.THIN);
                    alignStyle.setBorderRight(BorderStyle.THIN);
                    alignStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    alignStyle.setWrapText(true); // 设置换行
                    for (int i = 0; i < list.size(); i++) {
                        AnnoTabtionData data = list.get(i);
                        cell = row.createCell(data.getCellIndex());
                        cell.setCellValue(data.getCellName());
                        // 设置样式
                        cell.setCellStyle(alignStyle);
                        sheet.setColumnWidth(i, data.getCellWidth());
                    }
                    // 填充数据

                    List<T> listData = excelData.getData();
                    if (listData != null) {
                        for (int rowNum = 0; rowNum < listData.size(); rowNum++) {
                            T entity = listData.get(rowNum);
                            HSSFRow _titleRow = sheet.createRow(hang + rowNum + 1);
                            for (AnnoTabtionData data : list) {
                                cell = _titleRow.createCell(data.getCellIndex());
                                cell.setCellValue(getValue(entity, data));
                                // 设置样式
                                cell.setCellStyle(alignStyle);
                            }
                        }
                    }
                }
            }
            String path = pathGeneral(filePath);
            out = new FileOutputStream(path);
            out.flush();
            wb.write(out);
            return removeLocalHome(path);
        } finally {
            if (out != null) {
                out.close();
            }
        }
    }

    /**
     * 追加文件
     *
     * @param filePath
     * @param inputStream
     * @param excelParams
     * @param <T>
     * @return
     * @throws Exception
     */
    private <T> String optXlsxFile(String filePath, FileInputStream inputStream, ExcelParams<T> excelParams)
            throws Exception {
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
        FileOutputStream out = null;
        // 遍历sheet页
        if (excelParams.getSheetData() != null) {
            for (String sheetName : excelParams.getSheetData().keySet()) {
                ExcelData<T> excelData = excelParams.getSheetData().get(sheetName);
                // 选中sheet
                XSSFSheet xssfSheet = xssfWorkbook.getSheet(sheetName);
                int hang = xssfSheet.getLastRowNum();
                if (hang <= 0) {
                    hang = 0;
                }
                // 生成表头
                List<AnnoTabtionData> list = findExcelTitle(excelData.getmClassEntity());
                if (hang > 0) {
                    hang = hang + 1;
                }
                XSSFRow titleRow = xssfSheet.createRow(hang);
                XSSFCell cell = null;
                // 创建样式
                XSSFCellStyle alignStyle = (XSSFCellStyle) xssfWorkbook.createCellStyle();
                alignStyle.setAlignment(HorizontalAlignment.CENTER);
                alignStyle.setBorderBottom(BorderStyle.THIN);
                alignStyle.setBorderTop(BorderStyle.THIN);
                alignStyle.setBorderLeft(BorderStyle.THIN);
                alignStyle.setBorderRight(BorderStyle.THIN);
                alignStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                alignStyle.setWrapText(true); // 设置换行
                XSSFFont font2 = xssfWorkbook.createFont();
                font2.setFontName("宋体");
                font2.setBold(true);// 粗体显示
                font2.setFontHeightInPoints((short) 10);
                alignStyle.setFont(font2);
                int x = 0;
                for (AnnoTabtionData data : list) {
                    cell = titleRow.createCell(data.getCellIndex());
                    cell.setCellValue(data.getCellName());
                    // 设置样式
                    cell.setCellStyle(alignStyle);
                    xssfSheet.setColumnWidth(x, data.getCellName().getBytes().length * 256);
                    x++;
                }
                // 填充数据
                XSSFCellStyle _alignStyle = (XSSFCellStyle) xssfWorkbook.createCellStyle();
                _alignStyle.setAlignment(HorizontalAlignment.CENTER);
                _alignStyle.setBorderBottom(BorderStyle.THIN);
                _alignStyle.setBorderTop(BorderStyle.THIN);
                _alignStyle.setBorderLeft(BorderStyle.THIN);
                _alignStyle.setBorderRight(BorderStyle.THIN);
                _alignStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                _alignStyle.setWrapText(true); // 设置换行
                List<T> listData = excelData.getData();
                int y = 0;
                if (listData != null && listData.size() > 0) {
                    for (int rowNum = 0; rowNum < listData.size(); rowNum++) {
                        T entity = listData.get(rowNum);
                        XSSFRow _titleRow = xssfSheet.createRow(hang + rowNum + 1);
                        for (AnnoTabtionData data : list) {
                            cell = _titleRow.createCell(data.getCellIndex());
                            String _value = getValue(entity, data);
                            cell.setCellValue(_value);
                            // 设置样式
                            cell.setCellStyle(_alignStyle);
                            xssfSheet.setColumnWidth(y, _value.getBytes().length * 256);
                            y++;
                        }

                    }
                }
            }
        }

        String path = pathGeneral(filePath);
        try {
            out = new FileOutputStream(path);
            xssfWorkbook.write(out);
            out.flush();
        } finally {
            if (out != null) {
                out.close();
            }
            xssfWorkbook.close();
        }
        return removeLocalHome(path);
    }

    /**
     * 反射获取值(追加数据)
     *
     * @param <T>
     * @param entity
     * @param data
     * @return
     */
    private <T> String getValue(T entity, AnnoTabtionData data) {
        Field[] fields = entity.getClass().getDeclaredFields();
        String value = "";
        for (Field field : fields) {
            field.setAccessible(true);
            if (field.getName().equals(data.getCellField())) {
                try {
                    value = field.get(entity) + "";
                } catch (Exception e) {
                    logger.error(e.getMessage());
                }
                break;
            }
        }
        return value;
    }

    /**
     * 处理xls
     *
     * @param filePath
     * @param params
     * @throws Exception
     */
    private String replaceXls(String filePath, ExcelReplaceParams params) throws Exception {
        POIFSFileSystem ps = new POIFSFileSystem(new FileInputStream(localStoragePath(null) + "/" + filePath)); // 使用POI提供的方法得到excel的信息
        FileOutputStream out = null;
        try (HSSFWorkbook wb = new HSSFWorkbook(ps)) {
            if (params.getParams() != null) {
                for (String sheetName : params.getParams().keySet()) {
                    Map<String, Object> _paramsValue = params.getParams().get(sheetName);
                    HSSFSheet sheet = wb.getSheet(sheetName); // 获取到工作表，因为一个excel可能有多个工作表
                    int rowNum = sheet.getLastRowNum();
                    for (int rowIndex = 0; rowIndex < rowNum; rowIndex++) {
                        HSSFRow row = sheet.getRow(rowIndex);
                        int cellIndex = row.getLastCellNum();
                        for (int cellNum = 0; cellNum < cellIndex; cellNum++) {
                            HSSFCell cell = row.getCell(cellNum);
                            String value = getCellValue(cell);
                            if (StringUtils.isNotEmpty(value)) {
                                // 遍历替换值
                                for (String key : _paramsValue.keySet()) {
                                    if (value.trim().contains("${" + key + "}")) {
                                        value = value.replaceAll("${" + key + "}", _paramsValue.get(key).toString());
                                        cell.setCellValue(value);
                                    }
                                }
                            }
                        }
                    }

                }
            }
            String path = pathGeneral(filePath);
            out = new FileOutputStream(path);
            out.flush();
            wb.write(out);
            out.close();
            return removeLocalHome(path);
        } finally {
            if (out != null) {
                out.close();
            }
        }
    }

    /**
     * 处理xlsx
     *
     * @param filePath
     * @param params
     * @throws Exception
     */
    private String replaceXlsx(String filePath, ExcelReplaceParams params) throws Exception {
        try (XSSFWorkbook xssfWorkbook = new XSSFWorkbook(
                new FileInputStream(localStoragePath(null) + "/" + filePath));) {
            if (params.getParams() != null) {
                for (String sheetName : params.getParams().keySet()) {
                    Map<String, Object> _paramsValue = params.getParams().get(sheetName);
                    XSSFSheet sheet = xssfWorkbook.getSheet(sheetName); // 获取到工作表，因为一个excel可能有多个工作表
                    int rowNum = sheet.getLastRowNum();
                    for (int rowIndex = 0; rowIndex < rowNum; rowIndex++) {
                        XSSFRow row = sheet.getRow(rowIndex);
                        int cellIndex = row.getLastCellNum();
                        for (int cellNum = 0; cellNum < cellIndex; cellNum++) {
                            XSSFCell cell = row.getCell(cellNum);
                            String value = getCellValue(cell) + "";
                            if (StringUtils.isNotEmpty(value)) {
                                // 遍历替换值
                                for (String key : _paramsValue.keySet()) {
                                    if (value.trim().contains("${" + key + "}")) {
                                        StringBuilder modelContent = new StringBuilder("${");
                                        modelContent.append(key).append("}");
                                        value = value.replace(modelContent.toString(),
                                                _paramsValue.get(key).toString());
                                        cell.setCellValue(value);
                                    }
                                }
                            }
                        }
                    }
                }
                String path = pathGeneral(filePath);
                FileOutputStream out = new FileOutputStream(path);
                xssfWorkbook.write(out);
                out.flush();
                out.close();
                xssfWorkbook.close();
                return removeLocalHome(path);
            }
        }
        return null;
    }

    /**
     * 获取值
     *
     * @param cell
     * @return
     */
    private String getCellValue(Cell cell) {
        String cellValue = "";
        DecimalFormat df = new DecimalFormat("#");
        switch (cell.getCellType()) {
            case STRING:
                cellValue = cell.getRichStringCellValue().getString().trim();
                break;
            case NUMERIC:
                cellValue = df.format(cell.getNumericCellValue()).toString();
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
                break;
            case FORMULA:
                cellValue = cell.getCellFormula();
                break;
            default:
                cellValue = "";
        }
        return cellValue;
    }

    /**
     * 路径生成器
     *
     * @param filePath
     * @return
     */
    private String pathGeneral(String filePath) {
        File file = new File(localStoragePath(null) + "/" + filePath);
        String fileName = file.getName();
        String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
        String path = file.getParent() + "/" + file.getName().replace("." + suffix, "_") + System.currentTimeMillis()
                + "." + suffix;
        return path;
    }

    /**
     * 返回path路径
     *
     * @param filePath
     * @return
     */
    private String removeLocalHome(String filePath) {
        filePath = filePath.replaceAll("\\\\", "/");
        return filePath.replaceAll(localStoragePath(null), "");
    }

}
